package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Sites;

public class SitesDAO {
	
	private  Database db;
	
	public  SitesDAO(Database db) {
		this.db = db;
	}
	/**
     * a method for get the sites by siteId in database
     * @return sites where are in the site
     */
	  //getSitesData
	  public Map<String, Object> findSitesBySiteId(int pnSiteId) {
	        String vaSql = "SELECT * FROM sites WHERE site_id = ?";
	        Map<String, Object> result = db.querySingle(vaSql,pnSiteId);
	        if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
	    }
	  
	  public List<Map<String, Object>> findSite() {
	    	String vaSql = "SELECT site_id, site_name FROM sites ORDER BY site_name ASC";
	    	List<Map<String, Object>> result = db.queryList(vaSql);
	        if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
	    }
	  
	  //update  SitesData
	  public void updateSites(int pnSiteId,Sites poSites) {
		  
	    	String vaSql  = " UPDATE SITES SET " +
	    				  " SITE_NAME = ? , " +
	    				  " TAX_ID  = ? , " +
	    				  " TAX_BRANCH = ? , "+
	    				  " ADDRESS = ? , "+
	    				  " CITY_CODE = ? , "+
	    				  " DISTRICT_CODE = ? , "+
	    				  " PROVINCE_CODE = ? , "+
	    				  " POST_CODE = ? , "+
	    				  " TELEPHONE = ? , "+
	    				  " FAX = ? , "+
	    				  " HEAD_NAME = ? , "+
	    				  " HEAD_POSITION = ? , "+
	    				  " HOME_NUMBER = ? , "+
	    				  " UPDATE_USER_ID = ? , "+
	    				  " UPDATE_DATE = ? , " +
	    				  " UPDATE_TIME = ? " +
	    				  " WHERE " + 
	    				  " SITE_ID  = ? " ;
	    	
	    	 try {
					db.update(vaSql, poSites.getVaSiteName(), 
							poSites.getVaTaxId(),
							poSites.getVaTaxBranch(),
							poSites.getVaAddress(),
							poSites.getVaCityCode(),
							poSites.getVaDistrictCode(),
							poSites.getVaProvinceCode(),
							poSites.getVaPostCode(),
							poSites.getVaTelephone(),
							poSites.getVaFax(),
							poSites.getVaHeadName(),
							poSites.getVaHeadPosition(),
							poSites.getVaHomeNumber(),
							poSites.getVnUpdateUserId(),
							poSites.getVaUpdateDate(),
							poSites.getVaUpdateTime(),
							pnSiteId);
								
			}catch (Exception e) {
				throw new RuntimeException(e);
			}
	    }
	  
	  //add sies data
	  public void addSites(Sites poSites) {
	    	 String vaSql = "INSERT INTO SITES (SITE_ID,SITE_NAME,TAX_ID,TAX_BRANCH,ADDRESS,CITY_CODE,"
	    	 						+ "DISTRICT_CODE,PROVINCE_CODE,POST_CODE,TELEPHONE,FAX,"
	    	 						+ "HEAD_NAME,HEAD_POSITION,HOME_NUMBER,"
	    	 						+ "INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
	    	 						+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME,PACKAGE_ID)"
	    			 				+"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	    			try {
	    					db.insert(vaSql, poSites.getVnSiteId(),
	    							poSites.getVaSiteName(), 
	    							poSites.getVaTaxId(),
	    							poSites.getVaTaxBranch(),
	    							poSites.getVaAddress(),
	    							poSites.getVaCityCode(),
	    							poSites.getVaDistrictCode(),
	    							poSites.getVaProvinceCode(),
	    							poSites.getVaPostCode(),
	    							poSites.getVaTelephone(),
	    							poSites.getVaFax(),
	    							poSites.getVaHeadName(),
	    							poSites.getVaHeadPosition(),
	    							poSites.getVaHomeNumber(),
	    							poSites.getVnInsertUserId(),
	    							poSites.getVaInsertDate(),
	    							poSites.getVaInsertTime(),
	    							poSites.getVnUpdateUserId(),
	    							poSites.getVaUpdateDate(),
	    							poSites.getVaUpdateTime(),
	    							poSites.getPackageId());
	    				
	    			}catch (Exception e) {
	    	        	throw new RuntimeException(e);
	    	        }
	    	}
	  
	  // Louis 7-11-2014
	  public List<Map<String, Object>> findAll() 
	  {
		  try
		  {
			  String vaSql = "SELECT * FROM sites order by site_id ";
			  List<Map<String, Object>> result = db.queryList(vaSql);
			  if (result != null) {
				  return result;
			  } else {
				  return null;
			  }
		  }
		  catch(Exception ex)
		  {
			  return null;
		  }
	  }
	  
	  //CApe 17/11/2557
	  public List<Map<String, Object>> findAllJoinPackage() 
	  {
		  try
		  {
			  String vaSql = "SELECT * FROM sites s left OUTER JOIN sm_package p ON s.package_id = p.package_id order by s.site_id ";
			  List<Map<String, Object>> result = db.queryList(vaSql);
			  if (result != null) {
				  return result;
			  } else {
				  return null;
			  }
		  }
		  catch(Exception ex)
		  {
			  return null;
		  }
	  }
	  
	  
	  // Louis 7-11-2014
	  public boolean isDuplicate(int pnSiteId, String paSiteName) {
	        String vaSql = "SELECT * FROM sites WHERE site_id = ? OR site_name = ? ";
	        Map<String, Object> result = db.querySingle(vaSql,pnSiteId, paSiteName);
	        if (result != null && result.size() > 0) {
	            return true;
	        } else {
	            return false;
	        }
	    }
	  
	  // Louis 7-11-2014 *** อัพเดท site_id ด้วย  อันข้างบนไม่ได้รวม site_id
	  public void updateSitesAllAtributes(int pnOldSiteId,Sites poSites) {
		  
	    	String vaSql  = " UPDATE SITES SET " +
	    				  " SITE_ID = ? , " +
	    				  " SITE_NAME = ? , " +
	    				  " TAX_ID  = ? , " +
	    				  " TAX_BRANCH = ? , "+
	    				  " ADDRESS = ? , "+
	    				  " CITY_CODE = ? , "+
	    				  " DISTRICT_CODE = ? , "+
	    				  " PROVINCE_CODE = ? , "+
	    				  " POST_CODE = ? , "+
	    				  " TELEPHONE = ? , "+
	    				  " FAX = ? , "+
	    				  " HEAD_NAME = ? , "+
	    				  " HEAD_POSITION = ? , "+
	    				  " HOME_NUMBER = ? , "+
	    				  " UPDATE_USER_ID = ? , "+
	    				  " UPDATE_DATE = ? , " +
	    				  " UPDATE_TIME = ? ," +
	    				  " PACKAGE_ID = ? " +
	    				  " WHERE " + 
	    				  " SITE_ID  = ? " ;
	    	
	    	 try {
					db.update(vaSql, 
							poSites.getVnSiteId(),
							poSites.getVaSiteName(), 
							poSites.getVaTaxId(),
							poSites.getVaTaxBranch(),
							poSites.getVaAddress(),
							poSites.getVaCityCode(),
							poSites.getVaDistrictCode(),
							poSites.getVaProvinceCode(),
							poSites.getVaPostCode(),
							poSites.getVaTelephone(),
							poSites.getVaFax(),
							poSites.getVaHeadName(),
							poSites.getVaHeadPosition(),
							poSites.getVaHomeNumber(),
							poSites.getVnUpdateUserId(),
							poSites.getVaUpdateDate(),
							poSites.getVaUpdateTime(),
							poSites.getPackageId(),
							pnOldSiteId);
								
			}catch (Exception e) {
				throw new RuntimeException(e);
			}
	    }
	  
	  public void deleteSite(int pnSiteId)
	  {
		  try
		  {
		    	String vaSql  = " DELETE FROM SITES " +
	    				  " WHERE SITE_ID  = ? " ;
		    	
		    	db.update(vaSql, pnSiteId);
		  }
		  catch(Exception ex)
		  {
			  throw new RuntimeException(ex);
		  }
	  }
	  
}
